package com.vecspace.data.model;

import java.beans.IntrospectionException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.vecspace.database.model.PojoManager;
import org.apache.commons.lang3.StringUtils;

import com.vecspace.exception.HException;
import com.vecspace.jsmodel.SqlParam;
import com.vecspace.utils.Convert;
import com.vecspace.utils.DateTimeUtil;
import com.vecspace.utils.SqlUtil;

import comvecspace.data.model.sql.SqlParamPlaceholder;
import comvecspace.data.model.sql.TDataType;
import comvecspace.data.model.sql.TSqlOperator;
/** 查询字段及查询操作、查询值
 * @version: 1.0
 * @author: wanghua
 */
public class FilterColumn {
	private String columnName;
	private String columnExpression;
	private String dataType;
	private String operator;
	private String queryValue;
	public static String splitChar = "|";
	public FilterColumn(){
		
	}
	
	/**
	 * 设置查询字段
	 * @param columnName 字段名称
	 * @param dataType  数据类型
	 * @param operator  操作符，
	 * @param queryValue 查询值
	 * @author wanghua
	 */
	public FilterColumn(String columnName, TDataType dataType, TSqlOperator operator, String queryValue){
		this.columnName = columnName;
		this.dataType = dataType.toString();
		this.operator = operator.toString();
		this.queryValue = queryValue;
	}
	
	public FilterColumn(String columnName, TDataType dataType, TSqlOperator operator, Date queryValue){
		this.columnName = columnName;
		this.dataType = dataType.toString();
		this.operator = operator.toString();
		this.queryValue = DateTimeUtil.formatDateTime(queryValue);
	}
	/**
	 * 设置查询字段
	 * @param columnName 字段名称
	 * @param dataType  数据类型
	 * @param operator  操作符，
	 * @param queryValue 查询值
	 * @author wanghua
	 */
	public FilterColumn(String columnName, String dataType, String operator, String queryValue){
		this.columnName = columnName;
		this.dataType = dataType;
		this.operator = operator;
		this.queryValue = queryValue;
	}
	
	public String getColumnName() {
		return columnName;
	}
	public void setColumnName(String columnName) {
		this.columnName = columnName;
	}
	public String getDataType() {
		return dataType;
	}
	public void setDataType(String dataType) {
		this.dataType = dataType;
	}
	public String getOperator() {
		return operator;
	}
	public void setOperator(String operator) {
		this.operator = operator;
	}
	public String getQueryValue() {
		return queryValue;
	}
	public void setQueryValue(String queryValue) {
		this.queryValue = queryValue;
	}
	
	public String getColumnExpression() {
		return columnExpression;
	}

	public void setColumnExpression(String columnExpression) {
		this.columnExpression = columnExpression;
	}

	/**
	 * 把当前参数转为标准sql查询格式
	 * 功能描述：
	 * @return
	 * @throws Exception 
	 */
	private SqlParam toSqlParam(SqlParamPlaceholder sqlParamPlaceholder) throws Exception{
		if(StringUtils.isEmpty(queryValue)) {
			return null;
		}
		if(StringUtils.isEmpty(columnName)) {
			return null;
		}
		if(!SqlUtil.isValidSqlDML(columnName)) {
			return null;
		}
		columnName = SqlUtil.SqlCharFilter(columnName);
		
		SqlParam sqlParam = new SqlParam();
		String dataType2 = dataType;
		if(StringUtils.isEmpty(dataType2))
		 {
			dataType2 = "string";//默认字段类型为string
		}
		if(StringUtils.isEmpty(operator))
		 {
			operator = "eq";//默认查询条件为等于
		}
		dataType2 = dataType2.toLowerCase();
		operator = operator.toLowerCase();
		String sqlOp = "";

		TDataType ftDataType = TDataType.parseOf(dataType2);
		
		ArrayList<Object> params = new ArrayList<Object>();
		String[] paramArr = queryValue.split("\\|");//in 查询的时候，多个参数值默认用 | 进行分割。
		if(TDataType.ftString.equals(ftDataType)){//字符串类型
			for(String paramStr : paramArr){
				if(StringUtils.isEmpty(paramStr)) {
					continue;
				}
				params.add(paramStr);
			}
		}else if(TDataType.ftNumber.equals(ftDataType)){ //数字Number类型的
			for(String paramStr : paramArr){
				if(StringUtils.isEmpty(paramStr)) {
					continue;
				}
				if(paramStr.equalsIgnoreCase("null")){
					params.add("null");
				}else{
					try{
						if(paramStr.indexOf(".")>=0){
							Double num = Double.parseDouble(paramStr);
							params.add(num);
						}else{
							int num = Integer.parseInt(paramStr);
							params.add(num);
						}
					}catch(Exception e){
						e.printStackTrace();
					}
				}
			}
		}else if(TDataType.ftDouble.equals(ftDataType)){ //数字Double类型的
			for(String paramStr : paramArr){
				if(StringUtils.isEmpty(paramStr)){
					continue;
				}
				if(paramStr.equalsIgnoreCase("null")){
					params.add("null");
				}else{
					try{
						Double num = Double.parseDouble(paramStr);
						params.add(num);
					}catch(Exception e){
						e.printStackTrace();
					}
				}
			}
		}else if(TDataType.ftByte.equals(ftDataType)){ //数字Byte类型的
			for(String paramStr : paramArr){
				if(StringUtils.isEmpty(paramStr)) {
					continue;
				}
				if(paramStr.equalsIgnoreCase("null")){
					params.add("null");
				}else{
					try{
						Byte num = Byte.parseByte(paramStr);
						params.add(num);
					}catch(Exception e){
						e.printStackTrace();
					}
				}
			}
		}else if(TDataType.ftShort.equals(ftDataType)){ //数字Short类型的
			for(String paramStr : paramArr){
				if(StringUtils.isEmpty(paramStr)) {
					continue;
				}
				if(paramStr.equalsIgnoreCase("null")){
					params.add("null");
				}else{
					try{
						Short num = Short.parseShort(paramStr);
						params.add(num);
					}catch(Exception e){
						e.printStackTrace();
					}
				}
			}
		}else if(TDataType.ftInteger.equals(ftDataType)){ //数字Integer类型的
			for(String paramStr : paramArr){
				if(StringUtils.isEmpty(paramStr)) {
					continue;
				}
				if(paramStr.equalsIgnoreCase("null")){
					params.add("null");
				}else{
					try{
						Integer num = Integer.parseInt(paramStr);
						params.add(num);
					}catch(Exception e){
						e.printStackTrace();
					}
				}
			}
		}else if(TDataType.ftLong.equals(ftDataType)){ //数字Long类型的
			for(String paramStr : paramArr){
				if(StringUtils.isEmpty(paramStr)) {
					continue;
				}
				if(paramStr.equalsIgnoreCase("null")){
					params.add("null");
				}else{
					try{
						Long num = Long.parseLong(paramStr);
						params.add(num);
					}catch(Exception e){
						e.printStackTrace();
					}
				}
			}
		}else if(TDataType.ftBoolean.equals(ftDataType)){
			if(paramArr.length == 1 && "true".equalsIgnoreCase(paramArr[0])){
				params.add(1);//默认使用int存储bool值，并且0=false，1=true
				this.operator = "=";
			}else{
				return null;//当不为true时（checkbox不勾选），表示不使用这个查询条件。
			}
		}else if(TDataType.ftDate.equals(ftDataType) || TDataType.ftDateMonth.equals(ftDataType) || TDataType.ftDateYear.equals(ftDataType)){//日期类型
			boolean isFirstDateParam = false;	//第一次个日期
			boolean isBetweenType = false;		//日期区间查询

			if(TDataType.ftDateMonth.equals(ftDataType) || TDataType.ftDateYear.equals(ftDataType)){
				isFirstDateParam = true;
				isBetweenType = true;
				operator = "between";
				if(paramArr.length == 1){
					paramArr = new String[]{paramArr[0], paramArr[0]};
				}
			}

			if("between".equals(this.operator) && paramArr.length == 2) {
				isFirstDateParam = true;
				isBetweenType = true;
			}

			for(String paramStr : paramArr){
				if(StringUtils.isEmpty(paramStr)) {
					continue;
				}
				if(paramStr.equalsIgnoreCase("null")){
					params.add("null");
					if(isFirstDateParam)
						isFirstDateParam = false;
				}else{
					/*
					 * 解决日期区间查询问题：
					 * 当要查询条件为2015-09-01~2015-09-30时，不能查询到2015-09-30的数据
					 */
					if(isBetweenType) {
						if(isFirstDateParam) {
							if(paramStr.length() == 4){
								paramStr += "-01";
							}
							if(paramStr.length() == 6 || paramStr.length() == 7){//只有 yyyy-MM
								paramStr += "-01";//起始日为当月第一天
							}
							if(!paramStr.contains(":")) {
								paramStr = paramStr + " 00:00:00";
							}
							isFirstDateParam = false;
						} else {
							if(paramStr.length() == 4){
								paramStr += "-12";
							}
							if(paramStr.length() == 6 || paramStr.length() == 7){//只有 yyyy-MM
								int year = Integer.parseInt(paramStr.substring(0, 4));
								int month = Integer.parseInt(paramStr.substring(5));
								paramStr = DateTimeUtil.getLastDateStrOfMonth(year, month);//截止日为当月最后一天
							}
							if(!paramStr.contains(":")) {
								paramStr = paramStr + " 23:59:59";
							}
						}
					}
					try{
						Date datetime = DateTimeUtil.parseDate(paramStr);
						params.add(datetime);
					}catch(Exception e){
						e.printStackTrace();
					}
				}
			}			
		}else{
			throw new HException("不能识别的查询字段类型："+dataType2+",期望类型是：string,number,date");
		}
		if(params.size()==0) {
			return null;
		}
		String sql = "";
		if(columnExpression != null && columnExpression.trim().length()>0){
			sql = columnExpression;
		}else{
			sql = columnName;
		}
		TSqlOperator sqlOperator = TSqlOperator.parseOf(operator);
		
		if(TSqlOperator.opEqual.equals(sqlOperator)){
			sqlOp = "=";
			sql = sql +" "+ sqlOp + sqlParamPlaceholder.getNextPlaceholder();
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
			sqlParam.addParameter(params.get(0));
		}else if(TSqlOperator.opNotEqual.equals(sqlOperator)){
			sqlOp = "<>";
			sql = sql +" "+ sqlOp + sqlParamPlaceholder.getNextPlaceholder();
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
			sqlParam.addParameter(params.get(0));
		}else if(TSqlOperator.opLike.equals(sqlOperator)){
			sqlOp = "like";
			sql = sql +" "+ sqlOp + sqlParamPlaceholder.getNextPlaceholder();
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
			sqlParam.addParameter("%"+params.get(0)+"%");
		}else if(TSqlOperator.opLeftLike.equals(sqlOperator)){
			sqlOp = "like";
			sql = sql +" "+ sqlOp + sqlParamPlaceholder.getNextPlaceholder();
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
			sqlParam.addParameter(params.get(0)+"%");
		}else if(TSqlOperator.opRightLike.equals(sqlOperator)){
			sqlOp = "like";
			sql = sql +" "+ sqlOp + sqlParamPlaceholder.getNextPlaceholder();
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
			sqlParam.addParameter("%"+params.get(0));
		}else if(TSqlOperator.opIn.equals(sqlOperator)){//in 查询的参数化语句为 colname in (?,?,?)
			sqlOp = "in";
			sql = sql +" "+ sqlOp+" (";
			for(Object param : params){
				sql = sql + sqlParamPlaceholder.getNextPlaceholder() + ",";
				sqlParam.addParameter(param);
			}
			sql = sql.substring(0, sql.length()-1);
			sql = sql + ")";
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
		}else if(TSqlOperator.opGreater.equals(sqlOperator)){
			sqlOp = ">";
			sql = sql +" "+ sqlOp + sqlParamPlaceholder.getNextPlaceholder();
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
			sqlParam.addParameter(params.get(0));
		}else if(TSqlOperator.opLess.equals(sqlOperator)){
			sqlOp = "<";
			sql = sql +" "+ sqlOp + sqlParamPlaceholder.getNextPlaceholder();
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
			sqlParam.addParameter(params.get(0));
		}else if(TSqlOperator.opGreaterOrEqual.equals(sqlOperator)){
			sqlOp = ">=";
			sql = sql +" "+ sqlOp + sqlParamPlaceholder.getNextPlaceholder();
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
			sqlParam.addParameter(params.get(0));
		}else if(TSqlOperator.opLessOrEqual.equals(sqlOperator)){
			sqlOp = "<=";
			sql = sql +" "+ sqlOp + sqlParamPlaceholder.getNextPlaceholder();
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
			sqlParam.addParameter(params.get(0));
		}else if(TSqlOperator.opBetween.equals(sqlOperator)){
			if(params.size() == 1){
				params.add("null");
			}
			if(params.size() == 2) {
				boolean allNull = "null".equals(params.get(0)) && "null".equals(params.get(1));//两个值都是null
				if (allNull) {
					return null;
				}
				if ("null".equalsIgnoreCase(Convert.toString(params.get(0), "null"))) { //[null, value]
					sql = sql + " <= " + sqlParamPlaceholder.getNextPlaceholder();
					sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
					sqlParam.addParameter(params.get(1));
				} else if ("null".equalsIgnoreCase(Convert.toString(params.get(1), "null"))) {//[value,null]
					sql = sql + " >= " + sqlParamPlaceholder.getNextPlaceholder();
					sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
					sqlParam.addParameter(params.get(0));
				} else if (!"null".equalsIgnoreCase(Convert.toString(params.get(0), "null"))
						|| !"null".equalsIgnoreCase(Convert.toString(params.get(1), "null"))) {
					sql = sql + " between " + sqlParamPlaceholder.getNextPlaceholder() + " and  " + sqlParamPlaceholder.getNextPlaceholder();
					sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
					sqlParam.addParameter(params.get(0));
					sqlParam.addParameter(params.get(1));
				}
			}
		}else if(TSqlOperator.opIsNull.equals(sqlOperator)){
			sqlOp = " is null";
			sql = sql +" "+ sqlOp;
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
		}else if(TSqlOperator.opIsNotNull.equals(sqlOperator)){
			sqlOp = " is not null";
			sql = sql +" "+ sqlOp;
			sqlParam.setSql(sql, sqlParamPlaceholder.getCurrIndex());
		}else{
			throw new HException("不能识别的查询条件："+operator+"可以使用：equal,like,in,greater,less等");
		}
		return sqlParam;
	}
	
	/**
	 * 把多个查询条件组合成一个查询项
	 * 功能描述：
	 * @param queryFields
	 * @return
	 * @throws Exception 
	 */
	public static SqlParam toSqlParam(List<FilterColumn> queryFields) throws Exception {
		SqlParamPlaceholder sqlParamPlaceholder = new SqlParamPlaceholder(false, 0);
		return toSqlParam(queryFields, sqlParamPlaceholder);
	}
	
	public static SqlParam toHqlParam(List<FilterColumn> queryFields) throws Exception {
		SqlParamPlaceholder sqlParamPlaceholder = new SqlParamPlaceholder(true, 0);
		return toSqlParam(queryFields, sqlParamPlaceholder);
	}
	
	public static SqlParam toSqlParam(List<FilterColumn> queryFields, SqlParamPlaceholder sqlParamPlaceholder) throws Exception{
		if(queryFields == null || queryFields.size() == 0) {
			return new SqlParam();
		}
		List<SqlParam> params = new ArrayList<SqlParam>();
		for(FilterColumn queryField : queryFields){
			String colName = queryField.getColumnName();
			if("_".equals(colName) || "-".equals(colName)) {
				continue;
			}
			SqlParam p = queryField.toSqlParam(sqlParamPlaceholder);	
			if(p != null){
				params.add(p);
			}
		}
		SqlParam sqlparam = toSqlParam2(params);
		sqlparam.setMaxParamIndex(sqlParamPlaceholder.getCurrIndex());
		return sqlparam;
	}
	

	/**
	 * 把多个查询项组合成一个组合项
	 * 功能描述：
	 * @param params
	 * @return 
	 */
	private static SqlParam toSqlParam2(List<SqlParam> params){
		if(params == null || params.size() == 0) {
			return new SqlParam();
		}
		SqlParam sqlParam = new SqlParam();
		SqlParam sqlParam0 = params.get(0);
		String sql = sqlParam0.getSql();
		sqlParam.addParameters(sqlParam0.getParameter());
		
		for(int i=1;i<params.size();++i){ 
			sqlParam0 = params.get(i); 
			if(sqlParam0 == null) {
				continue;
			}
			sql = sql + " AND " + sqlParam0.getSql();
			sqlParam.addParameters(sqlParam0.getParameter());
		}
		
		sqlParam.setSql(sql, 0);
		return sqlParam;
	}
	

	/**将filterColumns中的ColumnName进行处理，如果值为propName，则替换为ColumnName
	 * 
	 * @param clazz
	 * @param filterColumns 
	 * @author wanghua
	 * @throws IntrospectionException 
	 * @reviewer  
	 * @createTime 2021年9月30日 下午4:30:35
	 */
	public static void replaceFilterColumnName(Class<?> clazz, List<FilterColumn> filterColumns) throws IntrospectionException {
		if(filterColumns == null || filterColumns.size() == 0)return;
		for(FilterColumn filterColumn : filterColumns) {
			String propName = filterColumn.getColumnName();
			String columnName;

			columnName = PojoManager.getColumnFilterName(clazz, propName, true);
			if(columnName == null || columnName.length() == 0) {
				columnName = propName;
			}
			filterColumn.setColumnName(columnName);			 
		}
	}
}

